<?php (defined('BASEPATH')) OR exit('No direct script access allowed');

class Timesheet_Model extends CI_Model {
	
	private $table= 'timesheets';
	private $shiftingModel;
	function __construct() {
		parent::__construct();
		$this->shiftingModel = $this->load->model('Shifting_Model');
		$this->clientModel = $this->load->model('Client_Model');
		$this->userModel = $this->load->model('User_Model');
		$this->dbGeneric = $this->load->model('Dbgeneric_Model');
		$this->load->helper('DateUtils');
		$this->load->helper('ConstantsUtils');
	}
	
	function timeInOut ($userId, $timeIn, $timeOut){
		$shiftId = $this->shiftingModel->getShiftId($userId);
		$data = array('time_in'=>$timeIn, 'time_out'=>$timeOut, 'user_id'=>$userId, 'shift_id'=>$shiftId);
		$this->db->insert($this->table, $data);
		return $this->db->insert_id();
	}

	function getLogsByPeriod($api, $payroll_period_id){
		//$this->db->select('*');
		//$query = $this->db->get_where('payroll_period', array('id' => $payroll_id));
		$payroll = $this->getObject('payroll_period', array('id' => $payroll_period_id));
		if($payroll !== false){
			$date_start = $payroll->date_start;
			$date_end = $payroll->date_end;
			
			list($a,$code) = explode("-",$api);
			$data = array('payroll_period_id'=>$payroll_period_id);
			$this->db->select("t.*, TIMEDIFF(`t`.`time_out`,`t`.`time_in` ) as diff, r.remarks_name");
			$this->db->from('timesheets as t');
			$this->db->join('users as u','u.id=t.user_id','left');
			$this->db->join('clients as c','c.id=u.client_id','left');
			$this->db->join('timesheet_remarks as r','r.id=t.remarks','left');
			$this->db->where('c.api', $a); 
			$this->db->where('c.code', $code); 
			$this->db->where('t.time_in >=', $date_start);
			$this->db->where('t.time_out <=', $date_end);
			$query = $this->db->get();
			return $query->result();
		} else {
			return 0;
		}
	}
	
	function getLogsByPeriodSummary($api, $payroll_period_id){
		//$this->db->select('*');
		//$query = $this->db->get_where('payroll_period', array('id' => $payroll_id));
		
		$payroll = $this->getObject('payroll_period', array('id' => $payroll_period_id));
		if($payroll !== false){
			$date_start = $payroll->date_start;
			$date_end = $payroll->date_end;
			
			list($a,$code) = explode("-",$api);
			$data = array('payroll_period_id'=>$payroll_period_id);
			$this->db->select("t.user_id, f.first_name, f.last_name, f.middle_name, SEC_TO_TIME(SUM(TIME_TO_SEC(t.undertime))) as total_undertime, SEC_TO_TIME(SUM(TIME_TO_SEC(t.overtime))) as total_overtime, SEC_TO_TIME(SUM(TIME_TO_SEC(t.late))) as 
			total_late, SEC_TO_TIME(SUM(TIME_TO_SEC(t.earlier))) as total_earlier, 
			SEC_TO_TIME(SUM(TIME_TO_SEC(TIMEDIFF( `t`.`time_out`,`t`.`time_in` )))) as total_hours");
			$this->db->from('timesheets as t');
			$this->db->join('users as u','u.id=t.user_id','left');
			$this->db->join('file201 as f','f.user_id=u.id','left');
			$this->db->join('clients as c','c.id=u.client_id','left');
			$this->db->join('timesheet_remarks as r','r.id=t.remarks','left');
			$this->db->where('c.api', $a); 
			$this->db->where('c.code', $code); 
			$this->db->where('t.time_in >=', $date_start);
			$this->db->where('t.time_out <=', $date_end);
			$this->db->group_by('t.user_id');
			$this->db->order_by('t.user_id');
			$query = $this->db->get();
			
			$result_1 = $query->result();
			
			// ABSENT
			$this->db->select("t.user_id, COUNT(t.remarks) as num");
			$this->db->from('timesheets as t');
			$this->db->join('users as u','u.id=t.user_id','left');
			$this->db->join('clients as c','c.id=u.client_id','left');
			$this->db->join('timesheet_remarks as r','r.id=t.remarks','left');
			$this->db->where('c.api', $a); 
			$this->db->where('c.code', $code); 
			$this->db->where('t.date >=', $date_start);
			$this->db->where('t.date <=', $date_end);
			//$this->db->where('t.time_in >=', $date_start);
			//$this->db->where('t.time_out <=', $date_end);
			$this->db->where('t.remarks', 1);
			$this->db->group_by('t.user_id');
			$this->db->order_by('t.user_id');
			$query = $this->db->get();
			$result_2 = $query->result();
			
			
			// LEAVE WITH PAY
			$this->db->select("t.user_id, COUNT(t.remarks) as num");
			$this->db->from('timesheets as t');
			$this->db->join('users as u','u.id=t.user_id','left');
			$this->db->join('clients as c','c.id=u.client_id','left');
			$this->db->join('timesheet_remarks as r','r.id=t.remarks','left');
			$this->db->where('c.api', $a); 
			$this->db->where('c.code', $code); 
			$this->db->where('t.date >=', $date_start);
			$this->db->where('t.date <=', $date_end);
			//$this->db->where('t.time_in >=', $date_start);
			//$this->db->where('t.time_out <=', $date_end);
			$this->db->where('t.remarks', 2);
			$this->db->group_by('t.user_id');
			$this->db->order_by('t.user_id');
			$query = $this->db->get();
			$result_3 = $query->result();
			
			//LEAVE WITHOUT PAY
			$this->db->select("t.user_id, COUNT(t.remarks) as num");
			$this->db->from('timesheets as t');
			$this->db->join('users as u','u.id=t.user_id','left');
			$this->db->join('clients as c','c.id=u.client_id','left');
			$this->db->join('timesheet_remarks as r','r.id=t.remarks','left');
			$this->db->where('c.api', $a); 
			$this->db->where('c.code', $code); 
			$this->db->where('t.date >=', $date_start);
			$this->db->where('t.date <=', $date_end);
			//$this->db->where('t.time_in >=', $date_start);
			//$this->db->where('t.time_out <=', $date_end);
			$this->db->where('t.remarks', 3);
			$this->db->group_by('t.user_id');
			$this->db->order_by('t.user_id');
			$query = $this->db->get();
			$result_4 = $query->result();
			
			//WRONG CARD
			$this->db->select("t.user_id, COUNT(t.remarks) as num");
			$this->db->from('timesheets as t');
			$this->db->join('users as u','u.id=t.user_id','left');
			$this->db->join('clients as c','c.id=u.client_id','left');
			$this->db->join('timesheet_remarks as r','r.id=t.remarks','left');
			$this->db->where('c.api', $a); 
			$this->db->where('c.code', $code); 
			$this->db->where('t.date >=', $date_start);
			$this->db->where('t.date <=', $date_end);
			//$this->db->where('t.time_in >=', $date_start);
			//$this->db->where('t.time_out <=', $date_end);
			$this->db->where('t.remarks', 5);
			$this->db->group_by('t.user_id');
			$this->db->order_by('t.user_id');
			$query = $this->db->get();
			$result_5 = $query->result();
			
			return array('all'=>$result_1,
			'absences'=>$this->recordToArray($result_2),'leave_with_pay'=>$this->recordToArray($result_3),
			'leave_without_pay'=>$this->recordToArray($result_4),'wrong_card'=>$this->recordToArray($result_5),
			'period'=>$payroll);
		} else {
			return 0;
		}
	}
	
	private function recordToArray($result){
		$items = array();
		foreach($result as $r){
			$items["uid{$r->user_id}"] = $r->num;
		}
		return $items;
	}
	
	private function getObject($table, $filter=array()){
		$query = $this->db->get_where('payroll_period',$filter,1,0);
		$result = $query->result();
		if(!empty($result)){
			return $result[0];
		}else {
			return false;
		}
	}
	
	
	public function getLogsForPayroll($api, $payroll_period_id){
	$payroll = $this->getObject('payroll_period', array('id' => $payroll_period_id));
		if($payroll !== false){
			$date_start = $payroll->date_start;
			$date_end = $payroll->date_end;
			$days = DateUtils::getNumberOfDays($date_start, $date_end);
			$days  -= 2;
			//echo $days;
			list($a,$code) = explode("-",$api);
			$data = array('payroll_period_id'=>$payroll_period_id);
			//$this->db->select("t.user_id, f.first_name, f.last_name, f.middle_name,  (SELECT `hourly` FROM salary WHERE `user_id`=100) as reg_rate, SEC_TO_TIME(SUM(TIME_TO_SEC(t.undertime))) as total_undertime, SEC_TO_TIME(SUM(TIME_TO_SEC(t.overtime))) as total_overtime, (SUM(TIME_TO_SEC(t.late)/3600) * (SELECT `hourly` FROM salary WHERE `user_id`=100)) as total_late, SEC_TO_TIME(SUM(TIME_TO_SEC(t.earlier))) as total_earlier, SEC_TO_TIME(SUM(TIME_TO_SEC(TIMEDIFF ( t.time_out,t.time_in )))) as total_hours");
			$this->db->select("t.user_id, f.first_name, f.last_name, f.middle_name, SEC_TO_TIME(SUM(TIME_TO_SEC(t.undertime))) as total_undertime, 
			SEC_TO_TIME(SUM(TIME_TO_SEC(t.overtime))) as total_overtime, 
			SEC_TO_TIME(SUM(TIME_TO_SEC(t.earlier))) 
			as total_earlier, SEC_TO_TIME(SUM(TIME_TO_SEC(TIMEDIFF( `t`.`time_out`,`t`.`time_in` )))) as total_hours");
			$this->db->from('timesheets as t');
			$this->db->join('users as u','u.id=t.user_id','left');
			$this->db->join('file201 as f','f.user_id=u.id','left');
			$this->db->join('clients as c','c.id=u.client_id','left');
			$this->db->join('timesheet_remarks as r','r.id=t.remarks','left');
			$this->db->where('c.api', $a); 
			$this->db->where('c.code', $code); 
			$this->db->where('t.time_in >=', $date_start);
			$this->db->where('t.time_out <=', $date_end);
			$this->db->group_by('t.user_id');
			$this->db->order_by('t.user_id');
			$query = $this->db->get();
			
			$result_1 = $query->result();
			
			// ABSENT
			$this->db->select("t.user_id, COUNT(t.remarks) as num");
			$this->db->from('timesheets as t');
			$this->db->join('users as u','u.id=t.user_id','left');
			$this->db->join('clients as c','c.id=u.client_id','left');
			$this->db->join('timesheet_remarks as r','r.id=t.remarks','left');
			$this->db->where('c.api', $a); 
			$this->db->where('c.code', $code); 
			$this->db->where('t.date >=', $date_start);
			$this->db->where('t.date <=', $date_end);
			//$this->db->where('t.time_in >=', $date_start);
			//$this->db->where('t.time_out <=', $date_end);
			$this->db->where('t.remarks', 1);
			$this->db->group_by('t.user_id');
			$this->db->order_by('t.user_id');
			$query = $this->db->get();
			$result_2 = $query->result();
			
			
			// LEAVE WITH PAY
			$this->db->select("t.user_id, COUNT(t.remarks) as num");
			$this->db->from('timesheets as t');
			$this->db->join('users as u','u.id=t.user_id','left');
			$this->db->join('clients as c','c.id=u.client_id','left');
			$this->db->join('timesheet_remarks as r','r.id=t.remarks','left');
			$this->db->where('c.api', $a); 
			$this->db->where('c.code', $code); 
			$this->db->where('t.date >=', $date_start);
			$this->db->where('t.date <=', $date_end);
			//$this->db->where('t.time_in >=', $date_start);
			//$this->db->where('t.time_out <=', $date_end);
			$this->db->where('t.remarks', 2);
			$this->db->group_by('t.user_id');
			$this->db->order_by('t.user_id');
			$query = $this->db->get();
			$result_3 = $query->result();
			
			//LEAVE WITHOUT PAY
			$this->db->select("t.user_id, COUNT(t.remarks) as num");
			$this->db->from('timesheets as t');
			$this->db->join('users as u','u.id=t.user_id','left');
			$this->db->join('clients as c','c.id=u.client_id','left');
			$this->db->join('timesheet_remarks as r','r.id=t.remarks','left');
			$this->db->where('c.api', $a); 
			$this->db->where('c.code', $code); 
			$this->db->where('t.date >=', $date_start);
			$this->db->where('t.date <=', $date_end);
			//$this->db->where('t.time_in >=', $date_start);
			//$this->db->where('t.time_out <=', $date_end);
			$this->db->where('t.remarks', 3);
			$this->db->group_by('t.user_id');
			$this->db->order_by('t.user_id');
			$query = $this->db->get();
			$result_4 = $query->result();
			
			//WRONG CARD
			$this->db->select("t.user_id, COUNT(t.remarks) as num");
			$this->db->from('timesheets as t');
			$this->db->join('users as u','u.id=t.user_id','left');
			$this->db->join('clients as c','c.id=u.client_id','left');
			$this->db->join('timesheet_remarks as r','r.id=t.remarks','left');
			$this->db->where('c.api', $a); 
			$this->db->where('c.code', $code); 
			$this->db->where('t.date >=', $date_start);
			$this->db->where('t.date <=', $date_end);
			//$this->db->where('t.time_in >=', $date_start);
			//$this->db->where('t.time_out <=', $date_end);
			$this->db->where('t.remarks', 5);
			$this->db->group_by('t.user_id');
			$this->db->order_by('t.user_id');
			$query = $this->db->get();
			$result_5 = $query->result();
			
			return array('all'=>$result_1,
			'absences'=>$this->recordToArray($result_2),'leave_with_pay'=>$this->recordToArray($result_3),
			'leave_without_pay'=>$this->recordToArray($result_4),'wrong_card'=>$this->recordToArray($result_5),
			'period'=>$payroll);
		} else {
			return 0;
		}
	}
	public function getPayrollById($table, $filter){
		return $this->getObject($table, $filter);
	}
	
	function save ($data){
		$filter['user_id'] = $data['user_id'];
		$filter['date'] = $data['date'];
		
		$ts = $this->timesheetExist($filter);
		//($ts);
		if(isset($ts) && !empty($ts)){
			$ts_id = $ts[0]->id;
			$this->db->where('id',$ts_id);
			$this->db->update($this->table,$data);
			return $ts_id;
		} else {
			$this->db->insert($this->table, $data);
			return $this->db->insert_id();
		}
	}
	
	function getLogsByPeriodByUser($api, $payroll_period_id, $user_id){
		$payroll = $this->getObject('payroll_period', array('id' => $payroll_period_id));
		if($payroll !== false){
			$date_start = $payroll->date_start;
			$date_end = $payroll->date_end;
			
			list($a,$code) = explode("-",$api);
			//$data = array('payroll_id'=>$payroll_id);
			$this->db->select("t.*, f.*, t.time_in, t.time_out, t.undertime, t.overtime, t.late, t.earlier, f.first_name, f.last_name, f.middle_name, 
			SEC_TO_TIME(TIME_TO_SEC(TIMEDIFF(`t`.`time_out`,`t`.`time_in` ))) as total_hours, r.remarks_name");
			$this->db->from('timesheets as t');
			$this->db->join('users as u','u.id=t.user_id','left');
			$this->db->join('file201 as f','f.user_id=u.id','left');
			$this->db->join('clients as c','c.id=u.client_id','left');
			$this->db->join('timesheet_remarks as r','r.id=t.remarks','left');
			//$this->db->join('timesheet_remarks as r2','r2.id=t.bio_remarks','left');
			$this->db->where('c.api', $a); 
			$this->db->where('c.code', $code); 
			$this->db->where('t.date >=', $date_start);
			$this->db->where('t.date <=', $date_end);
			$this->db->where('t.user_id', $user_id);
			$query = $this->db->get();
			return $query->result();
		} else {
			return 0;
		}
	}
	
	function getSummaryLogsByUser($api, $payroll_period_id, $user_id){
		$payroll = $this->getObject('payroll_period', array('id' => $payroll_period_id));
		if($payroll !== false){
			$date_start = $payroll->date_start;
			$date_end = $payroll->date_end;
			
			list($a,$code) = explode("-",$api);
			
			// get number of absences..
			$absences = $this->getNumberOfAbsences($api, $payroll_period_id, $user_id);
			//if($absences > 0) $absences *= ConstantsUtils::$EMPLOYEE_REGULAR_HOURS;
			//$data = array('payroll_id'=>$payroll_id);
			$this->db->select("SEC_TO_TIME(SUM(TIME_TO_SEC(t.late))) as total_late, SEC_TO_TIME(SUM(TIME_TO_SEC(t.approved_overtime))) as total_approved_overtime,
			SEC_TO_TIME(SUM(TIME_TO_SEC(t.approved_night_differential))) as total_night_differential, (DATEDIFF(MAX(t.date),MIN(t.date)) + 1 - $absences) as total_days, 
			SEC_TO_TIME(SUM(TIME_TO_SEC(t.approved_late))) as total_approved_late, 
			SEC_TO_TIME(SUM(TIME_TO_SEC(t.approved_undertime))) as total_approved_undertime");
			$this->db->from('timesheets as t');
			$this->db->where('t.date >=', $date_start);
			$this->db->where('t.date <=', $date_end);
			$this->db->where('t.remarks !=', 1); // ABSENT
			$this->db->where('t.remarks !=', 5); // WRONG CARD
			$this->db->where('t.user_id', $user_id);
			$query = $this->db->get();
			return $query->result();
		} else {
			return 0;
		}
	}
	
	function getNumberOfAbsences($api, $payroll_period_id, $user_id){
		$payroll = $this->getObject('payroll_period', array('id' => $payroll_period_id));
		if($payroll !== false){
			$date_start = $payroll->date_start;
			$date_end = $payroll->date_end;
			
			list($a,$code) = explode("-",$api);
			//$data = array('payroll_id'=>$payroll_id);
			$this->db->select("count(t.remarks) as absences");
			$this->db->from('timesheets as t');
			$this->db->where('t.date >=', $date_start);
			$this->db->where('t.date <=', $date_end);
			$this->db->where('t.remarks =', 1); // ABSENT
			$this->db->where('t.user_id', $user_id);
			$query = $this->db->get();
			$count = $query->result();
			if(!empty($count))
				return $count[0]->absences;
			else 
				return 0;
			//return $query->result();
		} else {
			return 0;
		}
	}
	
	
	function getUserTotalLogs($api, $payroll_period_id, $user_id){
		$payroll = $this->getObject('payroll_period', array('id' => $payroll_period_id));
		$client = $this->clientModel->find($api);
		if($payroll !== false){
			$date_start = $payroll->date_start;
			$date_end = $payroll->date_end;

			$this->db->select("t.user_id, f.first_name, f.last_name, f.middle_name,  TIME_TO_SEC(t.undertime) as undertime, 
			TIME_TO_SEC(t.overtime) as overtime, TIME_TO_SEC(t.late) as late, 
			TIME_TO_SEC(t.earlier) as earlier, TIME_TO_SEC(TIMEDIFF(`t`.`time_out`,`t`.`time_in` )) as hours, 
			r.remarks_name as remarks, t.remarks as remarks_id");
			$this->db->from('timesheets as t');
			$this->db->join('users as u','u.id=t.user_id','left');
			$this->db->join('file201 as f','f.user_id=u.id','left');
			$this->db->join('clients as c','c.id=u.client_id','left');
			$this->db->join('timesheet_remarks as r','r.id=t.remarks','left');
			$this->db->where('c.id', $client->id); 
			$this->db->where('t.time_in >=', $date_start);
			$this->db->where('t.time_out <=', $date_end);
			$this->db->where('t.user_id', $user_id);
			//$this->db->group_by('t.user_id');
			$query = $this->db->get();
			
			return $query->result();
		} else {
			return 0;
		}
	}
	
	function getUserLogs($api, $payroll_period_id, $user_id){
		$payroll = $this->getObject('payroll_period', array('id' => $payroll_period_id));
		$client = $this->clientModel->find($api);
		if($payroll !== false){
			$date_start = $payroll->date_start;
			$date_end = $payroll->date_end;

			$this->db->select("t.user_id, f.first_name, f.last_name, f.middle_name,  TIME_TO_SEC(t.undertime) as undertime, 
			SEC_TO_TIME(SUM(TIME_TO_SEC(t.approved_overtime))) as overtime, 
			SEC_TO_TIME(SUM(TIME_TO_SEC(t.approved_night_differential))) as night_differential,
			TIME_TO_SEC(t.late) as late, 
			TIME_TO_SEC(t.earlier) as earlier, TIME_TO_SEC(TIMEDIFF(`t`.`time_out`,`t`.`time_in` )) as hours, 
			r.remarks_name as remarks, t.remarks as remarks_id");
			$this->db->from('timesheets as t');
			$this->db->join('users as u','u.id=t.user_id','left');
			$this->db->join('file201 as f','f.user_id=u.id','left');
			$this->db->join('clients as c','c.id=u.client_id','left');
			$this->db->join('timesheet_remarks as r','r.id=t.remarks','left');
			$this->db->where('c.id', $client->id); 
			$this->db->where('t.time_in >=', $date_start);
			$this->db->where('t.time_out <=', $date_end);
			$this->db->where('t.user_id', $user_id);
			$this->db->where('t.remarks !=', 5);
			$this->db->where('t.is_saved', 1);
			//$this->db->group_by('t.user_id');
			$query = $this->db->get();
			
			return $query->result();
		} else {
			return 0;
		}
	}
	
	function getUserRestDay($api, $payroll_period_id, $user_id){
		$payroll = $this->getObject('payroll_period', array('id' => $payroll_period_id));
		$client = $this->clientModel->find($api);
		if($payroll !== false){
			$date_start = $payroll->date_start;
			$date_end = $payroll->date_end;

			$user_rest_day = $this->userModel->getUserRestDay($api,$user_id);
			
			$this->db->select("
			count(t.date) as rest_day,
			SEC_TO_TIME(SUM(TIME_TO_SEC(t.approved_overtime))) as excess, SEC_TO_TIME(SUM(TIME_TO_SEC(t.approved_night_differential))) as night_differential");
			$this->db->from('timesheets as t');
			$this->db->join('users as u','u.id=t.user_id','left');
			$this->db->join('file201 as f','f.user_id=u.id','left');
			$this->db->join('clients as c','c.id=u.client_id','left');
			$this->db->join('timesheet_remarks as r','r.id=t.remarks','left');
			$this->db->where('c.id', $client->id); 
			$this->db->where('t.time_in >=', $date_start);
			$this->db->where('t.time_out <=', $date_end);
			//$this->db->where('t.time_in !=', ConstantsUtils::$EMPTY_DATE_TIME);
			//$this->db->where('t.time_out !=', ConstantsUtils::$EMPTY_DATE_TIME);
			$this->db->where('t.user_id', $user_id);
			$this->db->where('t.remarks !=', 5);
			$this->db->where('t.remarks !=', 1);
			$this->db->where('t.is_saved', 1);
			$this->db->where('DAYNAME(t.date)', $user_rest_day[0]->day);
			//$this->db->group_by('t.user_id');
			$query = $this->db->get();
			$result = $query->result();
			return (!empty($result))?$result[0]:false;
		} else {
			return 0;
		}
	}
	
	
	function getHolidayByType($api, $payroll_period_id, $user_id, $type){
		
		$payroll = $this->getObject('payroll_period', array('id' => $payroll_period_id));
		$client = $this->clientModel->find($api);
		
		if($payroll !== false){
			$date_start = $payroll->date_start;
			$date_end = $payroll->date_end;
			$filter = array('client_id'=>$client->id,'type'=>$type,'date_start'=>$date_start,'date_end'=>$date_end);
			$holidays = $this->clientModel->getClientHolidays($filter);
			return $holidays;
		} else {
			return 0;
		}
	}
	
	function getMemberWorkedHolidays($regular_holiday, $user_id){
		$count_days = 0;
		$ot = 0;
		$night_diff = 0;
		foreach($regular_holiday as $ts){
			$this->db->select('count(*) as days,SUM(TIME_TO_SEC(approved_overtime)) as holiday_ot, 
			SUM(TIME_TO_SEC(approved_night_differential)) as night_differential');
			$this->db->from('timesheets');
			$this->db->where("date IN ('".$ts->date_start."','". $ts->date_end ."')");
			$this->db->where('time_in !=', ConstantsUtils::$EMPTY_DATE_TIME);
			$this->db->where('time_out !=', ConstantsUtils::$EMPTY_DATE_TIME);
			$this->db->where('remarks !=', 1);
			$this->db->where('remarks !=', 5);
			$this->db->where('user_id =', $user_id);
			$query = $this->db->get();
			//echo $this->db->last_query(); 
			$result = $query->result();
			if(!empty($result)) {
				$count_days += (int)$result[0]->days;
				$ot+= (int)$result[0]->holiday_ot;
				$night_diff += (int)$result[0]->night_differential;
			}
		}
		return array('days'=>$count_days, 'excess'=>$ot/3600, 'night_differential'=> $night_diff / 3600);
		
	}
	
	
	
	function find($api,$user_id,$timesheet_id){
		$client = $this->clientModel->find($api);
		if(isset($client)){

			$this->db->select("t.*, c.id as client_id, f.first_name, f.last_name, f.middle_name, TIME_TO_SEC(TIMEDIFF( `t`.`time_out`,`t`.`time_in` ))/3600 as hours, 
			r.remarks_name as remarks_name");
			$this->db->from('timesheets as t');
			$this->db->join('users as u','u.id=t.user_id','left');
			$this->db->join('file201 as f','f.user_id=u.id','left');
			$this->db->join('clients as c','c.id=u.client_id','left');
			$this->db->join('timesheet_remarks as r','r.id=t.remarks','left');
			$this->db->where('c.id', $client->id); 
			$this->db->where('t.id', $timesheet_id); 
			$this->db->where('t.user_id', $user_id);
			//$this->db->group_by('t.user_id');
			$query = $this->db->get();
			
			return $query->result();
		} else {
			return 0;
		}
	}
	
	function computeTimesheetPerId($timesheet){
		$item = array();
		$break = 0;
		$diff_hours = 0;
		$overtime = 0;
		$date = $timesheet->date;
		$time_in = substr($timesheet->time_in,11);
		$time_out = substr($timesheet->time_out,11);
		$time_in_seconds = strtotime($timesheet->time_in);
		$time_out_seconds = strtotime($timesheet->time_out);
		$settings = $this->clientModel->getSettingsByClientId($timesheet->client_id);
		if(isset($settings) && !empty($settings)){
			$break = $settings[0]->working_hours + $settings[0]->break_hours;
		}
		
		//echo $time_in_seconds;
		if(!isset($timesheet->overtime)){
			if($time_out_seconds > 0 && $time_in_seconds > 0){
				$hr_overtime = ($time_out_seconds - $time_in_seconds) / 3600;
				//$overtime = $hr_overtime - $break;
				$overtime = substr(DateUtils::decimal_to_time($hr_overtime - $break),3).":00";
			}
		} else
		$overtime = $timesheet->overtime;
		
		
		//if($overtime < 0 )$overtime = 0 ;
		// compute differential
		$diff_date = $date ." ".$settings[0]->start_night_diff;
		$diff_date_seconds = strtotime($diff_date);
		if(!isset($timesheet->night_differential)) {
			if( $diff_date_seconds < $time_out_seconds) {
				//$diff_hours = ($time_out_seconds - $diff_date_seconds) / 3600;
				$diff = ($time_out_seconds - $diff_date_seconds) / 3600;
				$diff_hours = substr(DateUtils::decimal_to_time($diff),3).":00";
				// there's night differential
			}else{
				$diff_hours = ConstantsUtils::$EMPTY_TIME;
				
			}
		} else 
		$diff_hours = $timesheet->night_differential;
		
		
		//
		$item['date'] = $date;
		$item['time_in'] = $time_in;
		$item['time_out'] = $time_out;
		//$item['overtime'] = $timesheet->overtime;
		//$item['approved_overtime'] = $timesheet->approved_overtime;
		$item['overtime_hours'] = $overtime;
		$item['night_differential'] = $diff_hours;
		
		return $item;
		

	}
	
	function update($api, $params){
		$client = $this->clientModel->find($api);
		$id = array_pop ($params);

		if(isset($client) && $client->id > 0) {
			$this->db->where('id',$id);
			$this->db->update($this->table,$params);
			return true;
		}
		return false;
		
	}
	
	private function timesheetExist($filter){
		$this->db->select('t.*');
		$this->db->from('timesheets as t');
		$this->db->where('t.user_id',$filter['user_id']);
		$this->db->where('t.date',$filter['date']);
		$query = $this->db->get();
		return $query->result();
	}
}